package com.zglc.ms.shop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.zglc.ms.shop.model.Region;
import com.zglc.ms.shop.model.UserAddress;

@Repository
public class AddressDao {

	private Logger logger = LoggerFactory.getLogger(getClass());
	
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public List<Region> getAll() {
		final String sql = "SELECT id, parent_id, region_name, region_type FROM addr_region WHERE is_show = 1";
		try {
			return jdbcTemplate.query(sql, new RowMapper<Region>() {

				@Override
				public Region mapRow(ResultSet rs, int rowNum) throws SQLException {
					Region region = new Region();
					region.setId(rs.getInt("id"));
					region.setpId(rs.getInt("parent_id"));
					region.setName(rs.getString("region_name"));
					region.setType(rs.getInt("region_type"));
					return region;
				}
			});
		} catch (Exception e) {
			logger.error(e.getMessage());
			return new ArrayList<>();
		}

	}

	public Integer getAddressId(int userId, int provId, int cityId, int areaId, String detail, 
			String contact, String phone) {
		final String sql = "SELECT id FROM user_address WHERE user_id = ? AND prov_id = ? AND city_id = ? AND "
				+ " area_id = ? AND detail = ? AND contact = ? AND phone = ? LIMIT 1";
		try {
			Integer id = jdbcTemplate.queryForObject(sql, new Object[] { userId, provId, cityId, areaId, 
					detail, contact, phone },
					Integer.class);
			return id;
		} catch (Exception e) {
			logger.error(e.getMessage());
			return null;
		}
	}
	
	
	public UserAddress getAddressById(int id) {
		final String sql = "SELECT user_id, prov_id, city_id, area_id, detail, contact, phone FROM user_address WHERE id = ?";
		try {
			UserAddress address = jdbcTemplate.queryForObject(sql, new Object[] { id}, 
					 new RowMapper<UserAddress>() {

				@Override
				public UserAddress mapRow(ResultSet rs, int rowNum) throws SQLException {
					UserAddress userAddress = new UserAddress();
					userAddress.setId(id);
					userAddress.setProvId(rs.getInt("prov_id"));
					userAddress.setCityId(rs.getInt("city_id"));
					userAddress.setAreaId(rs.getInt("area_id"));
					userAddress.setDetail(rs.getString("detail"));
					userAddress.setContact(rs.getString("contact"));
					userAddress.setPhone(rs.getString("phone"));
					return userAddress;
				}
			});
			return address;
		} catch (Exception e) {
			logger.error(e.getMessage());
			return null;
		}
	}
	
	public UserAddress getLastestAddress(int userId) {
		final String sql = "SELECT id, user_id, prov_id, city_id, area_id, detail, contact, phone FROM user_address"
				+ " WHERE user_id = ? ORDER BY id DESC LIMIT 1";
		try {
			UserAddress address = jdbcTemplate.queryForObject(sql, new Object[] { userId }, 
					 new RowMapper<UserAddress>() {

				@Override
				public UserAddress mapRow(ResultSet rs, int rowNum) throws SQLException {
					UserAddress userAddress = new UserAddress();
					userAddress.setId(rs.getInt("id"));
					userAddress.setProvId(rs.getInt("prov_id"));
					userAddress.setCityId(rs.getInt("city_id"));
					userAddress.setAreaId(rs.getInt("area_id"));
					userAddress.setDetail(rs.getString("detail"));
					userAddress.setContact(rs.getString("contact"));
					userAddress.setPhone(rs.getString("phone"));
					return userAddress;
				}
			});
			return address;
		} catch (Exception e) {
			logger.error(e.getMessage());
			return null;
		}
	}
	
	public boolean update(int id, int provId, int cityId, int areaId, String detail, 
			String contact, String phone) {
		final String sql = "UPDATE user_address SET prov_id = ?, city_id = ?, area_id = ?, "
				+ "detail = ?, contact = ?, phone = ?, updated_at = NOW() WHERE id = ?";
		try {
			jdbcTemplate.update(sql, provId, cityId, areaId, detail, contact, phone, id);
			return true;
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
		
		return false;
	}
	
	public Integer save(int userId, int provId, int cityId, int areaId, String detail, 
			String contact, String phone) {
		final String sql = "INSERT INTO user_address (user_id, prov_id, city_id, area_id, "
				+ "detail, contact, phone, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setInt(1, userId);
				ps.setInt(2, provId);
				ps.setInt(3, cityId);
				ps.setInt(4, areaId);
				ps.setString(5, detail);

				ps.setString(6, contact);
				ps.setString(7, phone);
				return ps;
			}
		}, keyHolder);

		return keyHolder.getKey().intValue();
	}
	
	public boolean isExist(int id, int userId) {
		final String sql = "SELECT id FROM user_address WHERE id = ? AND user_id = ? LIMIT 1";
		try {
			Integer result = jdbcTemplate.queryForObject(sql, new Object[] { id, userId },
					Integer.class);
			return (result != null) && (result > 0);
		} catch (Exception e) {
			logger.error(e.getMessage());
			return false;
		}
	}
	
	public void del(int id, int userId) {
		final String sql = "DELETE FROM user_address WHERE id = ? AND user_id = ?";
		try {
			jdbcTemplate.update(sql, new Object[]{id, userId});
		} catch (Exception e) {
			logger.error(e.getMessage());
		}
	}
	
	public List<UserAddress> getAddress(int userId) {
		final String sql = "SELECT id, prov_id, city_id, area_id, contact, phone, "
				+ "detail, created_at, updated_at FROM user_address WHERE user_id = ? ORDER BY id DESC";
		
		try {
			return jdbcTemplate.query(sql, new Object[]{userId}, new RowMapper<UserAddress>() {

				@Override
				public UserAddress mapRow(ResultSet rs, int rowNum) throws SQLException {
					UserAddress address = new UserAddress();
					address.setId(rs.getInt("id"));
					address.setProvId(rs.getInt("prov_id"));
					address.setCityId(rs.getInt("city_id"));
					address.setAreaId(rs.getInt("area_id"));
					address.setContact(rs.getString("contact"));
					address.setPhone(rs.getString("phone"));
					address.setDetail(rs.getString("detail"));
					address.setCreatedAt(rs.getDate("created_at"));
					address.setUpdatedAt(rs.getDate("updated_at"));
					return address;
				}
			});
		} catch (Exception e) {
			logger.error(e.getMessage());
			return new ArrayList<>();
		}
	}

}